create or replace view vac_report_templates_4_1 as
with tr as
(
select
 workspace,
 workspace_display_name,
 application_id,
 application_name,
 template_name,
 template_type,
 before_column_heading,
 column_heading_template,
 after_column_heading,
--
 ac_utils.hs(col_template1)                    col_template1_hs,
 dbms_lob.substr(col_template1,4000, 1)        col_template1_sr1,
 dbms_lob.substr(col_template1,4000, 4000+1 )  col_template1_sr2,
 dbms_lob.substr(col_template1,4000, 8000+1 )  col_template1_sr3,
 dbms_lob.substr(col_template1,4000, 12000+1)  col_template1_sr4,
 col_template_condition1,
 col_template_display_cond1,
--
 ac_utils.hs(col_template2)                    col_template2_hs,
 dbms_lob.substr(col_template2,4000, 1)        col_template2_sr1,
 dbms_lob.substr(col_template2,4000, 4000+1 )  col_template2_sr2,
 dbms_lob.substr(col_template2,4000, 8000+1 )  col_template2_sr3,
 dbms_lob.substr(col_template2,4000, 12000+1)  col_template2_sr4,
 col_template_condition2,
 col_template_display_cond2,
--
 ac_utils.hs(col_template3)                    col_template3_hs,
 dbms_lob.substr(col_template3,4000, 1)        col_template3_sr1,
 dbms_lob.substr(col_template3,4000, 4000+1 )  col_template3_sr2,
 dbms_lob.substr(col_template3,4000, 8000+1 )  col_template3_sr3,
 dbms_lob.substr(col_template3,4000, 12000+1)  col_template3_sr4,
 col_template_condition3,
 col_template_display_cond3,
--
 ac_utils.hs(col_template4)                    col_template4_hs,
 dbms_lob.substr(col_template4,4000, 1)        col_template4_sr1,
 dbms_lob.substr(col_template4,4000, 4000+1 )  col_template4_sr2,
 dbms_lob.substr(col_template4,4000, 8000+1 )  col_template4_sr3,
 dbms_lob.substr(col_template4,4000, 12000+1)  col_template4_sr4,
 col_template_condition4,
 col_template_display_cond4,
--
 col_template_before_rows,
 col_template_after_rows,
 col_template_before_first,
 col_template_after_last,
 pagination_template,
 next_page_template,
 previous_page_template,
 next_set_template,
 previous_set_template,
 row_style_mouse_over,
 row_style_checked,
 is_subscribed,
 subscribed_from,
 last_updated_by,
 last_updated_on,
 theme_number,
 theme_class,
 translate_this_template,
 component_comment
from APEX_APPLICATION_TEMP_REPORT t
)
select
 workspace,
 workspace_display_name,
 application_id,
 application_name,
 template_name,
 theme_number,
 last_updated_by,
 last_updated_on,
 u_name,
 u_value,
 to_clob(col_template1_sr1) || to_clob(col_template1_sr2)
 || to_clob(col_template1_sr3) || to_clob(col_template1_sr4) col_template1_sr,
--
 to_clob(col_template2_sr1) || to_clob(col_template2_sr2)
 || to_clob(col_template2_sr3) || to_clob(col_template2_sr4) col_template2_sr,
--
 to_clob(col_template3_sr1) || to_clob(col_template3_sr2)
 || to_clob(col_template3_sr3) || to_clob(col_template3_sr4) col_template3_sr,
--
 to_clob(col_template4_sr1) || to_clob(col_template4_sr2)
 || to_clob(col_template4_sr3) || to_clob(col_template4_sr4) col_template4_sr,
 'APEX_APPLICATION_TEMP_REPORT' src_view_name
from
(
  select
   workspace,
   workspace_display_name,
   application_id,
   application_name,
   template_name,
   theme_number,
   last_updated_by,
   last_updated_on,
   --
   col_template1_sr1,
   col_template1_sr2,
   col_template1_sr3,
   col_template1_sr4,
   --
   col_template2_sr1,
   col_template2_sr2,
   col_template2_sr3,
   col_template2_sr4,
   --
   col_template3_sr1,
   col_template3_sr2,
   col_template3_sr3,
   col_template3_sr4,
   --
   col_template4_sr1,
   col_template4_sr2,
   col_template4_sr3,
   col_template4_sr4,
   --
   u_name,
   u_value
  from tr
  UNPIVOT INCLUDE NULLS
   ( u_value
     FOR    u_name
     IN
     (
       template_type,
       before_column_heading,
       column_heading_template,
       after_column_heading,
  --
       col_template1_hs,
       col_template_condition1,
       col_template_display_cond1,
  --
       col_template2_hs,
       col_template_condition2,
       col_template_display_cond2,
  --
       col_template3_hs,
       col_template_condition3,
       col_template_display_cond3,
  --
       col_template4_hs,
       col_template_condition4,
       col_template_display_cond4,
  --
       col_template_before_rows,
       col_template_after_rows,
       col_template_before_first,
       col_template_after_last,
       pagination_template,
       next_page_template,
       previous_page_template,
       next_set_template,
       previous_set_template,
       row_style_mouse_over,
       row_style_checked,
       is_subscribed,
       subscribed_from,
       translate_this_template,
       component_comment
     )
   )
 ) t;

